﻿Imports System.Data
Imports System.Data.SqlClient
Imports Sistema_InformacionNaviera.BE
Imports Sistema_InformacionNavier.UTIL

Public Class DiasLibresDetalleDALC

    Public Function ListaDiasLibresDetalle(ByVal objDiaslibresDet As DiasLibresDetalleBE) As DataTable

        Dim sqlListar As String = String.Empty
        Dim objListaDLibresDet As New List(Of DiasLibresDetalleBE)
        Dim objListaDLibresDetBE As DiasLibresDetalleBE = Nothing
        Dim arrSqlParameter(0) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim blnExito As Boolean
        Dim strMensaje As String = String.Empty
        Dim intI As Integer = 0

        Try
            If objDiaslibresDet.Tipo = "CT" Then 'Contenedores
                sqlListar = "DiasLibresCtnr_Listar"
            ElseIf objDiaslibresDet.Tipo = "BL" Then 'Bill of lading
                sqlListar = "DiasLibresBL_Listar"
            ElseIf objDiaslibresDet.Tipo = "TP" Then 'Tipo Pies
                sqlListar = "Entidades_ListaDiasLibresTipoPies"
            ElseIf objDiaslibresDet.Tipo = "PT" Then 'Puerto
                sqlListar = "DiasLibresPuerto_Listar"
            ElseIf objDiaslibresDet.Tipo = "PA" Then 'Pais
                sqlListar = "Entidades_ListaDiasLibresPais"
            ElseIf objDiaslibresDet.Tipo = "AR" Then 'Area
                sqlListar = "DiasLibresArea_Listar"
            End If

            arrSqlParameter(0) = New SqlParameter("@CodAcu", System.Data.SqlDbType.VarChar)
            arrSqlParameter(0).Value = objDiaslibresDet.KdliDet
            objListaDLibresDet = New List(Of DiasLibresDetalleBE)()

            ds = objDatos.DevuelveDataSet(sqlListar, blnExito, strMensaje, arrSqlParameter)
            dt = ds.Tables(0)

            If dt.Rows.Count > 0 Then

                'While intI < dt.Rows.Count
                '    objListaDLibresDetBE = New DiasLibresDetalleBE()

                '    objListaDLibresDetBE.KdliDet = dt.Rows(intI).ItemArray(0).ToString
                '    If objDiaslibresDet.Tipo = "CT" Then 'Contenedores
                '        objListaDLibresDetBE.Viaje = dt.Rows(intI).ItemArray(1).ToString
                '        ' objListaDLibresDetBE.Nave = dt.Rows(intI).ItemArray(2).ToString
                '        objListaDLibresDetBE.Kbli = dt.Rows(intI).ItemArray(3).ToString
                '        objListaDLibresDetBE.PrefCtnr = dt.Rows(intI).ItemArray(5).ToString
                '        objListaDLibresDetBE.NumCtnr = dt.Rows(intI).ItemArray(5).ToString
                '        objListaDLibresDetBE.NroCtnr = dt.Rows(intI).ItemArray(5).ToString
                '        ' objListaDLibresDetBE.NroBL = dt.Rows(intI).ItemArray(4).ToString
                '        objListaDLibresDetBE.DiasDet = dt.Rows(intI).ItemArray(6).ToString
                '    ElseIf objDiaslibresDet.Tipo = "BL" Then 'Bill of lading
                '        objListaDLibresDetBE.Viaje = dt.Rows(intI).ItemArray(1).ToString
                '        objListaDLibresDetBE.Nave = dt.Rows(intI).ItemArray(2).ToString
                '        objListaDLibresDetBE.Kbli = dt.Rows(intI).ItemArray(3).ToString
                '        objListaDLibresDetBE.NroBL = dt.Rows(intI).ItemArray(4).ToString
                '        objListaDLibresDetBE.DiasDet = dt.Rows(intI).ItemArray(5).ToString
                '    ElseIf objDiaslibresDet.Tipo = "TP" Then 'Tipo Pies
                '        objListaDLibresDetBE.TipCar = dt.Rows(intI).ItemArray(1).ToString
                '        objListaDLibresDetBE.DesCar = dt.Rows(intI).ItemArray(2).ToString
                '        objListaDLibresDetBE.DesTPies = dt.Rows(intI).ItemArray(3).ToString
                '        objListaDLibresDetBE.TipCtnr = dt.Rows(intI).ItemArray(4).ToString
                '        objListaDLibresDetBE.Pies = dt.Rows(intI).ItemArray(5).ToString
                '        objListaDLibresDetBE.CTipo = dt.Rows(intI).ItemArray(6).ToString
                '        objListaDLibresDetBE.DiasDet = dt.Rows(intI).ItemArray(7).ToString
                '    ElseIf objDiaslibresDet.Tipo = "PT" Then 'Puerto
                '        objListaDLibresDetBE.CodPTo = dt.Rows(intI).ItemArray(1).ToString
                '        objListaDLibresDetBE.DesPto = dt.Rows(intI).ItemArray(2).ToString
                '        objListaDLibresDetBE.DiasDet = dt.Rows(intI).ItemArray(3).ToString
                '    ElseIf objDiaslibresDet.Tipo = "PA" Then 'Pais
                '        objListaDLibresDetBE.CodPais = dt.Rows(intI).ItemArray(1).ToString
                '        objListaDLibresDetBE.DesPais = dt.Rows(intI).ItemArray(2).ToString
                '        objListaDLibresDetBE.DiasDet = dt.Rows(intI).ItemArray(3).ToString
                '    ElseIf objDiaslibresDet.Tipo = "AR" Then 'Area
                '        objListaDLibresDetBE.CodArea = dt.Rows(intI).ItemArray(1).ToString
                '        objListaDLibresDetBE.Area = dt.Rows(intI).ItemArray(2).ToString
                '        objListaDLibresDetBE.DiasDet = dt.Rows(intI).ItemArray(3).ToString
                '    End If
                '    intI = intI + 1

                '    objListaDLibresDet.Add(objListaDLibresDetBE)
                'End While
            End If
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return dt
    End Function

    Public Function ValidarContenedor(ByVal objDiaslibresDet As DiasLibresDetalleBE) As DiasLibresDetalleBE
        Dim sqlListar As String
        Dim objDiaslibresDetBE = New DiasLibresDetalleBE()
        Dim objListaDLibresDet As New List(Of DiasLibresDetalleBE)
        Dim arrSqlParameter(2) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim blnExito As Boolean
        Dim strMensaje As String = String.Empty
        Dim intI As Integer = 0
        Dim iCuenta As Integer = 0
        Try
            sqlListar = "up_ValidaCntr"

            arrSqlParameter(0) = New SqlParameter("@kvje", System.Data.SqlDbType.Int)
            arrSqlParameter(0).Value = objDiaslibresDet.Viaje
            objListaDLibresDet = New List(Of DiasLibresDetalleBE)()

            arrSqlParameter(1) = New SqlParameter("@nroBL", System.Data.SqlDbType.VarChar)
            arrSqlParameter(1).Value = objDiaslibresDet.NroBL
            objListaDLibresDet = New List(Of DiasLibresDetalleBE)()

            arrSqlParameter(2) = New SqlParameter("@NroCntr", System.Data.SqlDbType.VarChar)
            arrSqlParameter(2).Value = objDiaslibresDet.NroCtnr
            objListaDLibresDet = New List(Of DiasLibresDetalleBE)()

            ds = objDatos.DevuelveDataSet(sqlListar, blnExito, strMensaje, arrSqlParameter)
            dt = ds.Tables(0)

            If dt.Rows.Count > 0 Then
                objDiaslibresDetBE.Viaje = dt.Rows(0).Item(0).ToString
                objDiaslibresDetBE.Kbli = dt.Rows(0).Item(1).ToString
            End If
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return objDiaslibresDetBE
    End Function

    Public Function ValidarBillOfLading(ByVal objDiaslibresDet As DiasLibresDetalleBE) As DiasLibresDetalleBE
        Dim sqlListar As String
        Dim objDiaslibresDetBE = New DiasLibresDetalleBE()
        Dim objListaDLibresDet As New List(Of DiasLibresDetalleBE)
        Dim arrSqlParameter(1) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim blnExito As Boolean
        Dim strMensaje As String = String.Empty
        Dim intI As Integer = 0
        Dim iCuenta As Integer = 0
        Try
            sqlListar = "up_ValidaBL"

            arrSqlParameter(0) = New SqlParameter("@kvje", System.Data.SqlDbType.Int)
            arrSqlParameter(0).Value = objDiaslibresDet.Viaje
            objListaDLibresDet = New List(Of DiasLibresDetalleBE)()

            arrSqlParameter(1) = New SqlParameter("@nroBL", System.Data.SqlDbType.VarChar)
            arrSqlParameter(1).Value = objDiaslibresDet.NroBL
            objListaDLibresDet = New List(Of DiasLibresDetalleBE)() 

            ds = objDatos.DevuelveDataSet(sqlListar, blnExito, strMensaje, arrSqlParameter)
            dt = ds.Tables(0)

            If dt.Rows.Count > 0 Then
                objDiaslibresDetBE.Viaje = dt.Rows(0).Item(0).ToString
                objDiaslibresDetBE.Kbli = dt.Rows(0).Item(1).ToString
            End If
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return objDiaslibresDetBE
    End Function

    Public Function ObtieneCodigo(ByVal objDiaslibresDet As DiasLibresDetalleBE) As List(Of DiasLibresDetalleBE)

        Dim sqlListar As String
        Dim objListaDLibresDet As New List(Of DiasLibresDetalleBE)
        Dim objListaDLibresDetBE As DiasLibresDetalleBE = Nothing
        Dim arrSqlParameter(1) As SqlParameter
        Dim ds As DataSet
        Dim dt As DataTable

        Dim objDatos As New AccesoDatos()
        Dim blnExito As Boolean
        Dim strMensaje As String = String.Empty
        Dim intI As Integer = 0

        Try
            sqlListar = "DiasLibres_ObtieneCodigoVarios"

            arrSqlParameter(0) = New SqlParameter("@TIPO", System.Data.SqlDbType.VarChar)
            arrSqlParameter(0).Value = objDiaslibresDet.Tipo
            objListaDLibresDet = New List(Of DiasLibresDetalleBE)()

            arrSqlParameter(1) = New SqlParameter("@COD", System.Data.SqlDbType.VarChar)
            arrSqlParameter(1).Value = objDiaslibresDet.CodArea
            objListaDLibresDet = New List(Of DiasLibresDetalleBE)()


            ds = objDatos.DevuelveDataSet(sqlListar, blnExito, strMensaje, arrSqlParameter)
            dt = ds.Tables(0)

            If dt.Rows.Count > 0 Then

                While intI < dt.Rows.Count
                    objListaDLibresDetBE = New DiasLibresDetalleBE()
                    objListaDLibresDetBE.CodPTo = dt.Rows(intI).ItemArray(0).ToString
                    intI = intI + 1
                    objListaDLibresDet.Add(objListaDLibresDetBE)
                End While
            End If
        Catch ex As Exception
            MsgBox(ex.Message, vbCritical, "Error del sistema")
            Return Nothing
            Exit Function
        End Try

        Return objListaDLibresDet
    End Function


    Public Function InsertarDiasLibresCntr(ByVal objDiaslibresDet As DiasLibresDetalleBE) As Boolean
        Dim objDatos As New AccesoDatos()
        ' Dim ds As DataSet
        'Dim dt As DataTable
        Dim arrSqlParameter(5) As SqlParameter
        Dim iKacu As Integer = 0
        Dim bExito As Boolean
        Dim strMensaje As String = String.Empty
        Try

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@KDli"
            arrSqlParameter(0).SqlDbType = SqlDbType.Int
            arrSqlParameter(0).Direction = ParameterDirection.Input
            arrSqlParameter(0).Value = objDiaslibresDet.KdliDet

            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@KVje"
            arrSqlParameter(1).SqlDbType = SqlDbType.Int
            arrSqlParameter(1).Direction = ParameterDirection.Input
            arrSqlParameter(1).Value = objDiaslibresDet.Viaje

            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@KBli"
            arrSqlParameter(2).SqlDbType = SqlDbType.Int 
            arrSqlParameter(2).Direction = ParameterDirection.Input
            arrSqlParameter(2).Value = objDiaslibresDet.Kbli

            arrSqlParameter(3) = New SqlParameter()
            arrSqlParameter(3).ParameterName = "@Pref"
            arrSqlParameter(3).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(3).Size = 4
            arrSqlParameter(3).Direction = ParameterDirection.Input
            arrSqlParameter(3).Value = objDiaslibresDet.PrefCtnr

            arrSqlParameter(4) = New SqlParameter()
            arrSqlParameter(4).ParameterName = "@Nume"
            arrSqlParameter(4).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(4).Size = 7
            arrSqlParameter(4).Direction = ParameterDirection.Input
            arrSqlParameter(4).Value = objDiaslibresDet.NumCtnr

            arrSqlParameter(5) = New SqlParameter()
            arrSqlParameter(5).ParameterName = "@Ucre"
            arrSqlParameter(5).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(5).Size = 15
            arrSqlParameter(5).Direction = ParameterDirection.Input
            arrSqlParameter(5).Value = objDiaslibresDet.Usu


            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("DiasLibresCnt_Insertar", bExito, strMensaje, arrSqlParameter)
            'sCodigo = objBLImpoBE.codPaquete
            Return bExito
             

        Catch ex As Exception
            Throw
        End Try

    End Function


    Public Function InsertarDiasLibresBl(ByVal objDiaslibresDet As DiasLibresDetalleBE) As Boolean
        Dim objDatos As New AccesoDatos()
        'Dim ds As DataSet
        'Dim dt As DataTable
        Dim arrSqlParameter(3) As SqlParameter
        Dim iKacu As Integer = 0
        Dim bExito As Boolean
        Dim strMensaje As String = String.Empty
        Try

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@KDli"
            arrSqlParameter(0).SqlDbType = SqlDbType.Int
            arrSqlParameter(0).Direction = ParameterDirection.Input
            arrSqlParameter(0).Value = objDiaslibresDet.KdliDet

            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@KVje"
            arrSqlParameter(1).SqlDbType = SqlDbType.Int
            arrSqlParameter(1).Direction = ParameterDirection.Input
            arrSqlParameter(1).Value = objDiaslibresDet.Viaje

            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@KBli"
            arrSqlParameter(2).SqlDbType = SqlDbType.Int 
            arrSqlParameter(2).Direction = ParameterDirection.Input
            arrSqlParameter(2).Value = objDiaslibresDet.Kbli
             
            arrSqlParameter(3) = New SqlParameter()
            arrSqlParameter(3).ParameterName = "@Ucre"
            arrSqlParameter(3).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(3).Size = 15
            arrSqlParameter(3).Direction = ParameterDirection.Input
            arrSqlParameter(3).Value = objDiaslibresDet.Usu


            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("DiasLibresBL_Insertar", bExito, strMensaje, arrSqlParameter)
            'sCodigo = objBLImpoBE.codPaquete
            Return bExito


        Catch ex As Exception
            Throw
        End Try

    End Function


    Public Function InsertarDiasLibresTipoPies(ByVal objDiaslibresDet As DiasLibresDetalleBE) As Boolean
        Dim objDatos As New AccesoDatos()
        'Dim ds As DataSet
        'Dim dt As DataTable
        Dim arrSqlParameter(4) As SqlParameter
        Dim iKacu As Integer = 0
        Dim bExito As Boolean
        Dim strMensaje As String = String.Empty
        Try

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@KDli"
            arrSqlParameter(0).SqlDbType = SqlDbType.Int
            arrSqlParameter(0).Direction = ParameterDirection.Input
            arrSqlParameter(0).Value = objDiaslibresDet.KdliDet

            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@Tcar"
            arrSqlParameter(1).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(1).Size = 2
            arrSqlParameter(1).Direction = ParameterDirection.Input
            arrSqlParameter(1).Value = objDiaslibresDet.TipCar

            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@Tcnt"
            arrSqlParameter(2).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(2).Size = 6
            arrSqlParameter(2).Direction = ParameterDirection.Input
            arrSqlParameter(2).Value = objDiaslibresDet.TipCtnr
             

            arrSqlParameter(3) = New SqlParameter()
            arrSqlParameter(3).ParameterName = "@Dias"
            arrSqlParameter(3).SqlDbType = SqlDbType.Int 
            arrSqlParameter(3).Direction = ParameterDirection.Input
            arrSqlParameter(3).Value = objDiaslibresDet.DiasDet

            arrSqlParameter(4) = New SqlParameter()
            arrSqlParameter(4).ParameterName = "@Ucre"
            arrSqlParameter(4).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(4).Size = 15
            arrSqlParameter(4).Direction = ParameterDirection.Input
            arrSqlParameter(4).Value = objDiaslibresDet.Usu


            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("DiasLibresTPies_Insertar", bExito, strMensaje, arrSqlParameter)
            'sCodigo = objBLImpoBE.codPaquete
            Return bExito


        Catch ex As Exception
            Throw
        End Try

    End Function

    Public Function InsertarDiasLibresPuerto(ByVal objDiaslibresDet As DiasLibresDetalleBE) As Boolean
        Dim objDatos As New AccesoDatos()
        Dim arrSqlParameter(2) As SqlParameter
        Dim iKacu As Integer = 0
        Dim bExito As Boolean
        Dim strMensaje As String = String.Empty
        Try

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@KDli"
            arrSqlParameter(0).SqlDbType = SqlDbType.Int
            arrSqlParameter(0).Direction = ParameterDirection.Input
            arrSqlParameter(0).Value = objDiaslibresDet.KdliDet

            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@Pto"
            arrSqlParameter(1).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(1).Size = 3
            arrSqlParameter(1).Direction = ParameterDirection.Input
            arrSqlParameter(1).Value = objDiaslibresDet.CodPTo

            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@Ucre"
            arrSqlParameter(2).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(2).Size = 15
            arrSqlParameter(2).Direction = ParameterDirection.Input
            arrSqlParameter(2).Value = objDiaslibresDet.Usu


            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("DiasLibresPuerto_Insertar", bExito, strMensaje, arrSqlParameter)
            'sCodigo = objBLImpoBE.codPaquete
            Return bExito


        Catch ex As Exception
            Throw
        End Try

    End Function

    Public Function InsertarDiasLibresPais(ByVal objDiaslibresDet As DiasLibresDetalleBE) As Boolean
        Dim objDatos As New AccesoDatos()
        'Dim ds As DataSet
        'Dim dt As DataTable
        Dim arrSqlParameter(2) As SqlParameter
        Dim iKacu As Integer = 0
        Dim bExito As Boolean
        Dim strMensaje As String = String.Empty
        Try

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@KDli"
            arrSqlParameter(0).SqlDbType = SqlDbType.Int
            arrSqlParameter(0).Direction = ParameterDirection.Input
            arrSqlParameter(0).Value = objDiaslibresDet.KdliDet

            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@Pais"
            arrSqlParameter(1).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(1).Size = 50
            arrSqlParameter(1).Direction = ParameterDirection.Input
            arrSqlParameter(1).Value = objDiaslibresDet.CodPais

            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@Ucre"
            arrSqlParameter(2).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(2).Size = 15
            arrSqlParameter(2).Direction = ParameterDirection.Input
            arrSqlParameter(2).Value = objDiaslibresDet.Usu


            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("DiasLibresPais_Insertar", bExito, strMensaje, arrSqlParameter)
            'sCodigo = objBLImpoBE.codPaquete
            Return bExito


        Catch ex As Exception
            Throw
        End Try

    End Function

    Public Function InsertarDiasLibresArea(ByVal objDiaslibresDet As DiasLibresDetalleBE) As Boolean
        Dim objDatos As New AccesoDatos()
       
        Dim arrSqlParameter(2) As SqlParameter
        Dim iKacu As Integer = 0
        Dim bExito As Boolean
        Dim strMensaje As String = String.Empty
        Try

            arrSqlParameter(0) = New SqlParameter()
            arrSqlParameter(0).ParameterName = "@KDli"
            arrSqlParameter(0).SqlDbType = SqlDbType.Int
            arrSqlParameter(0).Direction = ParameterDirection.Input
            arrSqlParameter(0).Value = objDiaslibresDet.KdliDet

            arrSqlParameter(1) = New SqlParameter()
            arrSqlParameter(1).ParameterName = "@Area"
            arrSqlParameter(1).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(1).Size = 50
            arrSqlParameter(1).Direction = ParameterDirection.Input
            arrSqlParameter(1).Value = objDiaslibresDet.CodArea

            arrSqlParameter(2) = New SqlParameter()
            arrSqlParameter(2).ParameterName = "@Ucre"
            arrSqlParameter(2).SqlDbType = SqlDbType.VarChar
            arrSqlParameter(2).Size = 15
            arrSqlParameter(2).Direction = ParameterDirection.Input
            arrSqlParameter(2).Value = objDiaslibresDet.Usu


            Dim oDatos As New AccesoDatos()

            oDatos.EjecutaSP("DiasLibresArea_Insertar", bExito, strMensaje, arrSqlParameter)
            'sCodigo = objBLImpoBE.codPaquete
            Return bExito


        Catch ex As Exception
            Throw
        End Try

    End Function

End Class
